﻿GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_UtilityAssetDistribution_UpdateParents')
	DROP PROCEDURE up_UtilityAssetDistribution_UpdateParents
GO

CREATE PROCEDURE [dbo].[up_UtilityAssetDistribution_UpdateParents]
(
	@parentsIds varchar(max)
)
AS

DECLARE @parentsIdsXml xml

SET @parentsIdsXml = CONVERT(xml, @parentsIds )


UPDATE 
	tb_Utility_Asset_Distributions
SET
	ParentAssetFk1  = CASE WHEN assets.ParentAssetFk1 = 0 THEN NULL ELSE assets.ParentAssetFk1 END
	,ParentAssetFk2 = CASE WHEN assets.ParentAssetFk2 = 0 THEN NULL ELSE assets.ParentAssetFk2 END
	,ParentAssetFk3 = CASE WHEN assets.ParentAssetFk3 = 0 THEN NULL ELSE assets.ParentAssetFk3 END
	,ParentAssetFk4 = CASE WHEN assets.ParentAssetFk4 = 0 THEN NULL ELSE assets.ParentAssetFk4 END
	,ParentAssetFk5 = CASE WHEN assets.ParentAssetFk5 = 0 THEN NULL ELSE assets.ParentAssetFk5 END
FROM	
	(
	SELECT
		Item.query('.').value('(asset/@id)[1]', 'int') as id
		,Item.query('.').value('(asset/parentAssetFk1)[1]', 'int') as ParentAssetFk1
		,Item.query('.').value('(asset/parentAssetFk2)[1]', 'int') as ParentAssetFk2	
		,Item.query('.').value('(asset/parentAssetFk3)[1]', 'int') as ParentAssetFk3
		,Item.query('.').value('(asset/parentAssetFk4)[1]', 'int') as ParentAssetFk4	
		,Item.query('.').value('(asset/parentAssetFk5)[1]', 'int') as ParentAssetFk5
	FROM
		@parentsIdsXml.nodes('/assets/asset') T(Item)
	) assets
WHERE
	tb_Utility_Asset_Distributions.Utility_Asset_DistributionId = assets.id




